#! /usr/bin/env python
# -*- coding: utf-8 -*-

##@package DBMoteReader
# @author David Salas and Bretton Murphy
# @author and Many furry animals.
# @date Spring 2011
#
# This module sends queries to a connected database and returns the values that it recieves back.
# After connecting to a database queries are sent by way of function call.  Which sensor and mote id
# data should be looked for are given to the function for formulate the proper query as well as a 
# optional time range.
#

import psycopg2
import adapt_table

##A mote reader that sends queries to a database to retrieve values from wireless sensor motes
#
# connectToDB() must first be called before trying to read sensor values.  Use the isConnected()
# method to verify that a good connection was established.
#
class DBMoteReader:

    ## Names of tables in the database.  
    tables = ('accel_x', 'accel_y', 'humidity', 'ir', 'light', 'mag_x', 'mag_y', 'temperature', 'motes', 'sensors', 'mote_conf')

    ## Default constructor, initialize instance variables to None
    def __init__(self):
        self.Database = None
        self.cursor = None

    ##Connect to a given database
    ## @param db The database name 
    # @param userName The user name set up for accessing the database
    # @param passWord The password set up for accessing the database
    # @param hostName The address to the database
    # Connects to a database using arguments that specify the database to connect to.
    # The arguments are all optional and will default to a database named drawbridge.
    #
    # @param db The database name 
    # @param userName The user name set up for accessing the database
    # @param passWord The password set up for accessing the database
    # @param hostName The address to the database
    #
    def connectToDB(self, db = 'drawbridge', userName = 'drbgreader', passWord = 'secret', hostName = 'neptune.cs.nmsu.edu'):

        # Connect to the database
        self.Database = psycopg2.connect(database = db, user = userName, password = passWord, host = hostName)
    
        # Create a Cursor to perform database operations
        self.cursor = self.Database.cursor()
    

    ##Disconnects from a database
    #
    # Checks first if there is a connection and if so disconnects from the database.
    #
    def disconnectDB(self):
        
        # close the connection to the database
        if self.cursor != None:
            self.cursor.close()
        if self.Database != None:
            self.Database.close()
         
    ##Checks the connection to a database
    #
    # Looks to see if there is a connection to a database or not.
    #
    # @return True if connected and False otherwise
    #
    def isConnected(self):
        
         if self.cursor != None and self.Database != None:
            return True
        
         return False

    ## 
    # This function should query the database for sensor values in the specified time range. If
    # no time range is specified it will return the most recent sensor value.  If a start time is
    # given but no end time is specified then the function will return all values from the start 
    # time up until and including the most recent sensor value.
    #
    # 
    # @param sensor The type of sensor to be read from. This is the string name of the sensor table.  All the tables are listed in the static variable tables.
    #
    # @param mote_id The ID of the mote we are querying.
    #
    # @param startTime The lower bound of the time range we want information from. This must be given as a python datetime object.  
    # EXAMPLE: datetime.datetime(2011, 4, 19, 15, 23, 12) 
    #   datetime.datetime(year, month, day, hour, minute, second)
    #
    # @param endTime The upper bound of the time range we want information from. This must a datetime object as above.
    #
    # @return Entries that match our mote ID, sensor type, and time range.
    #
    def getSensorData(self, sensor, mote_id, startTime=0, endTime=0):

        # Make sure input is valid
        if not (sensor in DBMoteReader.tables):
            return None

        # If both optional parameters(startTime,endTime) are left to default, we assume that the user is only
        # interested in the most recent value for the specified sensor. 
        if startTime==0 and endTime==0:
            self.cursor.execute("SELECT * FROM %s WHERE mote_id=%s ORDER BY time_st DESC LIMIT 1;", (sensor, mote_id))
            
        # If a start time is given but no end time is specified we return all values that range from the
        # startTime up to and including the most recent entry.
        elif startTime!=0 and endTime==0:
            self.cursor.execute("SELECT * FROM %s WHERE mote_id=%s AND time_st >= %s ORDER BY time_st ASC;", (sensor, mote_id, startTime))
        
        
        # Return all values that are in our time range for the specified mote and sensor 
        else:
            self.cursor.execute("SELECT * FROM %s WHERE mote_id=%s AND time_st BETWEEN %s AND %s ORDER BY time_st ASC;", (sensor, mote_id, startTime, endTime))
        
        # fetch the values
        values = self.cursor.fetchall()
        
        return values

      
   
      
    ##
    # Finds and returns the list of available sensors for a given mote.
    #
    # @param mote_id The ID of the mote we are interested in querying.
    #
    # @return The available sensors for the specified mote.
    def getSensors(self, mote_id):
  
      self.cursor.execute( "SELECT sensor_name,sensors.description FROM mote_conf, sensors WHERE sensor_id=id AND mote_id=%s;",(mote_id,))
        
      # fetch the values
      values = self.cursor.fetchall()
        
      return values
      
      
      
    ##
    # Finds and returns the list of available mote IDs and their descriptions.
    #
    # @return The available mote IDs in the database, with their locations.
    #  returned as tuples with the first element the id, location the second
    def getMotes(self):
  
      self.cursor.execute("SELECT id, location FROM motes;")
        
      # fetch the values
      values = self.cursor.fetchall()
        
      return values

